/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Employee;

import Address.AddressBean;
import Country.CountryBean;
import Department.DepartmentBean;
import Designation.DesignationBean;
import State.StateBean;
import User.UserBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(EmployeeBean eb) throws SQLException {
        int i = 0, k;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into employee  values(employeeid.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?)");
            pre.setString(1, eb.getEmployeeFatherName());
            pre.setInt(2, eb.getAddressId());
            pre.setString(3, eb.getPanCard());
            pre.setString(4, eb.getMobile());
            pre.setString(5, eb.getPhone());
            pre.setString(6, eb.getGender());
            pre.setString(7, eb.getHigherQuali());
            pre.setDate(8, eb.getDoj());
            pre.setString(9, eb.getJobdetails());
            pre.setInt(10, eb.getDepartmentId());
            pre.setInt(11, eb.getDesignationId());
            pre.setInt(12, eb.getUserId());
            pre.setString(13, eb.getPhoto());
            pre.setInt(14, eb.getEmployeeType());
            pre.setInt(15, eb.getEmployeeStatus());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(employee_id) from employee");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }

        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static EmployeeBean viewSpecific(String cname, String val) throws SQLException {
        EmployeeBean eb = new EmployeeBean();
        AddressBean ab = new AddressBean();
        StateBean sb = new StateBean();
        CountryBean cb = new CountryBean();
        DepartmentBean db = new DepartmentBean();
        DesignationBean dib = new DesignationBean();
        UserBean ub = new UserBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from employee left join address on employee.address_id=address.address_id left join country on address.country_id=country.country_id left join state on address.state_id=state.state_id left join department on employee.department_id=department.department_id left join designation on employee.designation_id=designation.designation_id left join users on employee.user_id=users.user_id where employee." + cname + "=?");
            pre.setString(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeFatherName(rs.getString(2));
                eb.setAddressId(rs.getInt(3));
                eb.setPanCard(rs.getString(4));
                eb.setMobile(rs.getString(5));
                eb.setPhone(rs.getString(6));
                eb.setGender(rs.getString(7));
                eb.setHigherQuali(rs.getString(8));
                eb.setDoj(rs.getDate(9));
                eb.setJobdetails(rs.getString(10));
                eb.setDepartmentId(rs.getInt(11));
                eb.setDesignationId(rs.getInt(12));
                eb.setUserId(rs.getInt(13));
                eb.setPhoto(rs.getString(14));
                eb.setEntryDate(rs.getDate(15));
                eb.setEmployeeType(rs.getInt(16));
                eb.setEmployeeStatus(rs.getInt(17));
                ab.setAddressId(rs.getInt(18));
                ab.setAddress1(rs.getString(19));
                ab.setAddress2(rs.getString(20));
                ab.setCountryId(rs.getInt(21));
                ab.setStateId(rs.getInt(22));
                ab.setPinCode(rs.getString(23));
                ab.setEntryDate(rs.getDate(24));
                cb.setCountryId(rs.getInt(25));
                cb.setCountryName(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                ab.setCountryBean(cb);
                sb.setStateId(rs.getInt(28));
                sb.setCountryId(rs.getInt(29));
                sb.setStateName(rs.getString(30));
                sb.setEntryDate(rs.getDate(31));
                ab.setStateBean(sb);
                eb.setAddressBean(ab);
                db.setDepartmentId(rs.getInt(32));
                db.setDepartmentName(rs.getString(33));
                db.setEntryDate(rs.getDate(34));
                eb.setDepartmentBean(db);
                dib.setDesignationId(rs.getInt(35));
                dib.setDesignation(rs.getString(36));
                dib.setEntryDate(rs.getDate(37));
                eb.setDesignationBean(dib);
                ub.setUserId(rs.getInt(38));
                ub.setDor(rs.getDate(39));
                ub.setPassword(rs.getString(40));
                ub.setUserType(rs.getString(41));
                ub.setEmail(rs.getString(42));
                ub.setEmployeeName(rs.getString(43));
                ub.setDob(rs.getDate(44));
                ub.setEntryDate(rs.getDate(45));
                eb.setUserBean(ub);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return eb;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from employee left join address on employee.address_id=address.address_id left join country on address.country_id=country.country_id left join state on address.state_id=state.state_id left join department on employee.department_id=department.department_id left join designation on employee.designation_id=designation.designation_id left join users on employee.user_id=users.user_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                EmployeeBean eb = new EmployeeBean();
                AddressBean ab = new AddressBean();
                StateBean sb = new StateBean();
                CountryBean cb = new CountryBean();
                DepartmentBean db = new DepartmentBean();
                DesignationBean dib = new DesignationBean();
                UserBean ub = new UserBean();
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeFatherName(rs.getString(2));
                eb.setAddressId(rs.getInt(3));
                eb.setPanCard(rs.getString(4));
                eb.setMobile(rs.getString(5));
                eb.setPhone(rs.getString(6));
                eb.setGender(rs.getString(7));
                eb.setHigherQuali(rs.getString(8));
                eb.setDoj(rs.getDate(9));
                eb.setJobdetails(rs.getString(10));
                eb.setDepartmentId(rs.getInt(11));
                eb.setDesignationId(rs.getInt(12));
                eb.setUserId(rs.getInt(13));
                eb.setPhoto(rs.getString(14));
                eb.setEntryDate(rs.getDate(15));
                eb.setEmployeeType(rs.getInt(16));
                eb.setEmployeeStatus(rs.getInt(17));
                ab.setAddressId(rs.getInt(18));
                ab.setAddress1(rs.getString(19));
                ab.setAddress2(rs.getString(20));
                ab.setCountryId(rs.getInt(21));
                ab.setStateId(rs.getInt(22));
                ab.setPinCode(rs.getString(23));
                ab.setEntryDate(rs.getDate(24));
                cb.setCountryId(rs.getInt(25));
                cb.setCountryName(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                ab.setCountryBean(cb);
                sb.setStateId(rs.getInt(28));
                sb.setCountryId(rs.getInt(29));
                sb.setStateName(rs.getString(30));
                sb.setEntryDate(rs.getDate(31));
                ab.setStateBean(sb);
                eb.setAddressBean(ab);
                db.setDepartmentId(rs.getInt(32));
                db.setDepartmentName(rs.getString(33));
                db.setEntryDate(rs.getDate(34));
                eb.setDepartmentBean(db);
                dib.setDesignationId(rs.getInt(35));
                dib.setDesignation(rs.getString(36));
                dib.setEntryDate(rs.getDate(37));
                eb.setDesignationBean(dib);
                ub.setUserId(rs.getInt(38));
                ub.setDor(rs.getDate(39));
                ub.setPassword(rs.getString(40));
                ub.setUserType(rs.getString(41));
                ub.setEmail(rs.getString(42));
                ub.setEmployeeName(rs.getString(43));
                ub.setDob(rs.getDate(44));
                ub.setEntryDate(rs.getDate(45));
                ub.setUserStatus(rs.getInt(46));
                ub.setEntryEmail(rs.getString(47));
                eb.setUserBean(ub);
                list.add(eb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static int edit(EmployeeBean eb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update employee set employee_father_name=?, address_id=?, pan_card=?, mobile=?, phone=?,gender=?, higher_qualification=?, doj=?, job_details=?, department_id=?, designation_id=?, user_id=?, photo=?, entrydate=sysdate, employee_type=?, employee_status=? where employee_id=?");
            pre.setString(1, eb.getEmployeeFatherName());
            pre.setInt(2, eb.getAddressId());
            pre.setString(3, eb.getPanCard());
            pre.setString(4, eb.getMobile());
            pre.setString(5, eb.getPhone());
            pre.setString(6, eb.getGender());
            pre.setString(7, eb.getHigherQuali());
            pre.setDate(8, eb.getDoj());
            pre.setString(9, eb.getJobdetails());
            pre.setInt(10, eb.getDepartmentId());
            pre.setInt(11, eb.getDesignationId());
            pre.setInt(12, eb.getUserId());
            pre.setString(13, eb.getPhoto());
            pre.setInt(14, eb.getEmployeeType());
            pre.setInt(15, eb.getEmployeeStatus());
            pre.setInt(16, eb.getEmployeeId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from employee where employee_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static EmployeeBean viewSpecific(String cname, int val) throws SQLException {
        EmployeeBean eb = new EmployeeBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from employee left join address on employee.address_id=address.address_id left join country on address.country_id=country.country_id left join state on address.state_id=state.state_id left join department on employee.department_id=department.department_id left join designation on employee.designation_id=designation.designation_id left join users on employee.user_id=users.user_id where employee." + cname + "=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AddressBean ab = new AddressBean();
                StateBean sb = new StateBean();
                CountryBean cb = new CountryBean();
                DepartmentBean db = new DepartmentBean();
                DesignationBean dib = new DesignationBean();
                UserBean ub = new UserBean();
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeFatherName(rs.getString(2));
                eb.setAddressId(rs.getInt(3));
                eb.setPanCard(rs.getString(4));
                eb.setMobile(rs.getString(5));
                eb.setPhone(rs.getString(6));
                eb.setGender(rs.getString(7));
                eb.setHigherQuali(rs.getString(8));
                eb.setDoj(rs.getDate(9));
                eb.setJobdetails(rs.getString(10));
                eb.setDepartmentId(rs.getInt(11));
                eb.setDesignationId(rs.getInt(12));
                eb.setUserId(rs.getInt(13));
                eb.setPhoto(rs.getString(14));
                eb.setEntryDate(rs.getDate(15));
                eb.setEmployeeType(rs.getInt(16));
                eb.setEmployeeStatus(rs.getInt(17));
                ab.setAddressId(rs.getInt(18));
                ab.setAddress1(rs.getString(19));
                ab.setAddress2(rs.getString(20));
                ab.setCountryId(rs.getInt(21));
                ab.setStateId(rs.getInt(22));
                ab.setPinCode(rs.getString(23));
                ab.setEntryDate(rs.getDate(24));
                cb.setCountryId(rs.getInt(25));
                cb.setCountryName(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                ab.setCountryBean(cb);
                sb.setStateId(rs.getInt(28));
                sb.setCountryId(rs.getInt(29));
                sb.setStateName(rs.getString(30));
                sb.setEntryDate(rs.getDate(31));
                ab.setStateBean(sb);
                eb.setAddressBean(ab);
                db.setDepartmentId(rs.getInt(32));
                db.setDepartmentName(rs.getString(33));
                db.setEntryDate(rs.getDate(34));
                eb.setDepartmentBean(db);
                dib.setDesignationId(rs.getInt(35));
                dib.setDesignation(rs.getString(36));
                dib.setEntryDate(rs.getDate(37));
                eb.setDesignationBean(dib);
                ub.setUserId(rs.getInt(38));
                ub.setDor(rs.getDate(39));
                ub.setPassword(rs.getString(40));
                ub.setUserType(rs.getString(41));
                ub.setEmail(rs.getString(42));
                ub.setEmployeeName(rs.getString(43));
                ub.setDob(rs.getDate(44));
                ub.setEntryDate(rs.getDate(45));
                eb.setUserBean(ub);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return eb;
    }
public static EmployeeBean viewSpecificLib(String cname, int val) throws SQLException {
        EmployeeBean eb = new EmployeeBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from employee left join address on employee.address_id=address.address_id left join country on address.country_id=country.country_id left join state on address.state_id=state.state_id left join department on employee.department_id=department.department_id left join designation on employee.designation_id=designation.designation_id left join users on employee.user_id=users.user_id where users."+cname+"=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AddressBean ab = new AddressBean();
                StateBean sb = new StateBean();
                CountryBean cb = new CountryBean();
                DepartmentBean db = new DepartmentBean();
                DesignationBean dib = new DesignationBean();
                UserBean ub = new UserBean();
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeFatherName(rs.getString(2));
                eb.setAddressId(rs.getInt(3));
                eb.setPanCard(rs.getString(4));
                eb.setMobile(rs.getString(5));
                eb.setPhone(rs.getString(6));
                eb.setGender(rs.getString(7));
                eb.setHigherQuali(rs.getString(8));
                eb.setDoj(rs.getDate(9));
                eb.setJobdetails(rs.getString(10));
                eb.setDepartmentId(rs.getInt(11));
                eb.setDesignationId(rs.getInt(12));
                eb.setUserId(rs.getInt(13));
                eb.setPhoto(rs.getString(14));
                eb.setEntryDate(rs.getDate(15));
                eb.setEmployeeType(rs.getInt(16));
                eb.setEmployeeStatus(rs.getInt(17));
                ab.setAddressId(rs.getInt(18));
                ab.setAddress1(rs.getString(19));
                ab.setAddress2(rs.getString(20));
                ab.setCountryId(rs.getInt(21));
                ab.setStateId(rs.getInt(22));
                ab.setPinCode(rs.getString(23));
                ab.setEntryDate(rs.getDate(24));
                cb.setCountryId(rs.getInt(25));
                cb.setCountryName(rs.getString(26));
                cb.setEntryDate(rs.getDate(27));
                ab.setCountryBean(cb);
                sb.setStateId(rs.getInt(28));
                sb.setCountryId(rs.getInt(29));
                sb.setStateName(rs.getString(30));
                sb.setEntryDate(rs.getDate(31));
                ab.setStateBean(sb);
                eb.setAddressBean(ab);
                db.setDepartmentId(rs.getInt(32));
                db.setDepartmentName(rs.getString(33));
                db.setEntryDate(rs.getDate(34));
                eb.setDepartmentBean(db);
                dib.setDesignationId(rs.getInt(35));
                dib.setDesignation(rs.getString(36));
                dib.setEntryDate(rs.getDate(37));
                eb.setDesignationBean(dib);
                ub.setUserId(rs.getInt(38));
                ub.setDor(rs.getDate(39));
                ub.setPassword(rs.getString(40));
                ub.setUserType(rs.getString(41));
                ub.setEmail(rs.getString(42));
                ub.setEmployeeName(rs.getString(43));
                ub.setDob(rs.getDate(44));
                ub.setEntryDate(rs.getDate(45));
                eb.setUserBean(ub);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return eb;
    }
}
